package no.gnet.edvd.database.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import javax.sql.DataSource;

import no.gnet.edvd.DataStore;
import no.gnet.edvd.engines.EventOdds;
import no.gnet.edvd.exception.MatchAlreadyImportedException;
import no.gnet.edvd.exception.NoSuchBettableEventException;
import no.gnet.edvd.exception.NoSuchImportedMatchException;
import no.gnet.edvd.exception.StorageException;
import no.gnet.edvd.feed.types.RemoteMatch;
import no.gnet.edvd.statemachine.BetState;
import no.gnet.edvd.types.Activity;
import no.gnet.edvd.types.ActivityType;
import no.gnet.edvd.types.Bet;
import no.gnet.edvd.types.Category;
import no.gnet.edvd.types.Comment;
import no.gnet.edvd.types.Currency;
import no.gnet.edvd.types.Event;
import no.gnet.edvd.types.EventType;
import no.gnet.edvd.types.FacebookUser;
import no.gnet.edvd.types.GenericEvent;
import no.gnet.edvd.types.MatchEvent;
import no.gnet.edvd.types.Option;
import no.gnet.edvd.types.PaymentAccount;
import no.gnet.edvd.types.PaymentProvider;
import no.gnet.edvd.types.Player;
import no.gnet.edvd.types.Transfer;
import no.gnet.edvd.types.Wallet;

import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.springframework.stereotype.Component;

@Component
public class MySQLDatastore implements DataStore {

	DataSource datasource;

	private static Logger logger = Logger.getLogger(MySQLDatastore.class);

	public MySQLDatastore() {
		logger.debug("MySQLDatastore created");
	}

	public MySQLDatastore(DataSource datasource) {
		this.datasource = datasource;
	}

	@Override
	public int placeBet(Bet bet) {
		String insertTableSQL = "INSERT INTO BET" + "(option_id, player_id, amount, currency) VALUES" + "(?,?,?,?)";

		String updatewalletsql = "update wallet set amount = amount - ? where id=? and userid=?";

		Connection conn = getDbConnection();
		PreparedStatement preparedStatementInsertBet = null;
		PreparedStatement preparedStatementUpdateWallet = null;

		int playerid = bet.player.id;
//		Wallet wallet = getWalletForUser(playerid);
//		if (wallet == null) {
//			throw new IllegalStateException("No wallet for player " + playerid);
//		}
		try {
//			conn.setAutoCommit(false); // yes, this needs to be a transaction
			preparedStatementInsertBet = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			preparedStatementInsertBet.setInt(1, bet.option.id);
			preparedStatementInsertBet.setInt(2, playerid);
			preparedStatementInsertBet.setDouble(3, bet.amount);
			preparedStatementInsertBet.setInt(4, bet.currency.id);
			preparedStatementInsertBet.executeUpdate();
			ResultSet rs = preparedStatementInsertBet.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
//
//			preparedStatementUpdateWallet = conn.prepareStatement(updatewalletsql);
//			preparedStatementUpdateWallet.setDouble(1, bet.amount);
//			preparedStatementUpdateWallet.setInt(2, wallet.id);
//			preparedStatementUpdateWallet.setInt(3, playerid);
//			preparedStatementUpdateWallet.executeUpdate();
//
//			conn.commit();

			return auto_id;
		} catch (SQLException e) {
			logger.error(e);
//			try {
//				conn.rollback();
//			} catch (SQLException e1) {
//				logger.fatal("Failed rollback, needs to be compensated", e);
//				throw new StorageException(e);
//			}
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatementInsertBet, preparedStatementUpdateWallet);
		}
	}

	private void closeGracefully(Connection conn, PreparedStatement... preparedStatements) {
		for (int i = 0; i < preparedStatements.length; i++) {
			PreparedStatement preparedStatement = preparedStatements[i];
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					logger.error(sqle);
				}
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException sqle) {
				logger.error(sqle);
			}
		}

	}

	private Connection getDbConnection() {
		try {
			return datasource.getConnection();
		} catch (SQLException e) {
			logger.error(e);
		}
		throw new StorageException("Failed to get connection to datasource");
	}

	@Override
	public void updateBet(Bet bet) {

	}

	@Override
	public Bet getBet(Bet bet) {
		return getBet(bet.id);
	}

	private Bet getBet(int betid) {
		String selectSQL = "SELECT eventid,option_id,optionname,player_id,amount,currency,"
				+ "shortname,eventdesc,firstname,lastname,email,timeofbet from bets_expanded " + "WHERE betid = ?";
		Bet bet = null;
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, betid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int amount = rs.getInt("amount");
				int currency = rs.getInt("currency");
				int option_id = rs.getInt("option_id");
				int player_id = rs.getInt("player_id");
				int eventid = rs.getInt("eventid");
				String firstname = rs.getString("firstname");
				String email = rs.getString("email");
				String lastname = rs.getString("lastname");
				String shortname = rs.getString("shortname");
				String eventdesc = rs.getString("eventdesc");
				String optionname = rs.getString("optionname");
				java.sql.Timestamp timeofbet = rs.getTimestamp("timeofbet");
				GenericEvent event = new GenericEvent();
				event.id = eventid;
				event.description = eventdesc;
				event.name = shortname;
				Player player = new Player(firstname, lastname, email);
				player.id = player_id;
				Option option = new Option();
				option.id = option_id;
				option.name = optionname;
				option.event = event;
				bet = new Bet(event, option, amount, currency == 1 ? Currency.NOK : Currency.BETCOIN, player,
						new DateTime(timeofbet.getTime()), betid);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return bet;
	}

	@Override
	public List<Bet> getBetsForEvent(Event event) {
		return getBetsForEvent(event.id);
	}

	@Override
	public List<Bet> getBetsForEvent(int eventid) {
		ArrayList<Bet> bets = new ArrayList<Bet>();
		String selectSQL = "SELECT betid,option_id,optionname,player_id,amount,currency,shortname,"
				+ "eventdesc,firstname,lastname,email,timeofbet from bets_expanded " + "WHERE eventid = ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int amount = rs.getInt("amount");
				int currency = rs.getInt("currency");
				int option_id = rs.getInt("option_id");
				int player_id = rs.getInt("player_id");
				int betid = rs.getInt("betid");
				String firstname = rs.getString("firstname");
				String email = rs.getString("email");
				String lastname = rs.getString("lastname");
				String shortname = rs.getString("shortname");
				String eventdesc = rs.getString("eventdesc");
				String optionname = rs.getString("optionname");
				java.sql.Timestamp timeofbet = rs.getTimestamp("timeofbet");
				GenericEvent event = new GenericEvent();
				event.id = eventid;
				event.description = eventdesc;
				event.name = shortname;
				Player player = new Player(firstname, lastname, email);
				player.id = player_id;
				Option option = new Option();
				option.id = option_id;
				option.name = optionname;
				option.event = event;
				Bet bet = new Bet(event, option, amount, currency == 1 ? Currency.NOK : Currency.BETCOIN, player,
						new DateTime(timeofbet.getTime()), betid);
				bets.add(bet);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return bets;
	}

	@Override
	public List<Bet> getAllBetsForPlayer(int playerid) {
		ArrayList<Bet> bets = new ArrayList<Bet>();
		String selectSQL = "SELECT ub.betid,ub.option_id,ub.optionname,ub.eventid,ub.eventState,"
				+ "ub.amount,ub.currency,ub.shortname,	ub.eventdesc,ub.firstname,ub.lastname,"
				+ "ub.email,ub.timeofbet,ub.win, t.amount as transferamount,t.completed "
				+ "from userbets ub left join transfer t on (ub.betid=t.bet) where player_id=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int amount = rs.getInt("amount");
				int currency = rs.getInt("currency");
				int betid = rs.getInt("betid");
				String firstname = rs.getString("firstname");
				String email = rs.getString("email");
				String lastname = rs.getString("lastname");
				java.sql.Timestamp timeofbet = rs.getTimestamp("timeofbet");
				GenericEvent event = new GenericEvent();
				event.id = rs.getInt("eventid");
				event.state = BetState.valueOf(rs.getString("eventState"));
				event.description = rs.getString("eventdesc");
				event.name = rs.getString("shortname");
				Player player = new Player(firstname, lastname, email);
				player.id = playerid;
				Option option = new Option();
				option.id = rs.getInt("option_id");
				option.name = rs.getString("optionname");
				option.event = event;
				Bet bet = new Bet(event, option, amount, currency == 1 ? Currency.NOK : Currency.BETCOIN, player,
						new DateTime(timeofbet.getTime()), betid);
				bet.iswin = rs.getBoolean("win");
				attachCompletedTransfers(rs, bet);
				bets.add(bet);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return bets;
	}

	private void attachCompletedTransfers(ResultSet rs, Bet bet) throws SQLException {
		String completed = rs.getString("completed");
		boolean transfercomplete = completed!=null && Integer.parseInt(completed)==1;
		if (transfercomplete) {
			Transfer t = new Transfer();
			t.setCompleted(transfercomplete);
			t.setAmount(rs.getDouble("transferamount"));
			bet.setCompletedTransfer(t);
		}
	}

	@Override
	public Event storeEvent(Event event) {
		long start = System.currentTimeMillis();
		Event insertedEvent = event;
		Connection conn = null;
		try {
			conn = getDbConnection();
			conn.setAutoCommit(false); // yes, this needs to be a transaction
			int event_id = _putEvent(event, conn);
			insertedEvent.id = event_id;
			for (Iterator<Option> iterator = insertedEvent.getOptions().iterator(); iterator.hasNext();) {
				Option option = iterator.next();
				option.event = insertedEvent;
				int oid = storeOption(option, conn);
				option.id = oid;
			}
			if (event.isPrivate() && event.getInvitedList() != null) {
				for (String email : event.getInvitedList()) {
					logger.info("Storing invited playeremail: " + email);
					storeInvite(event_id, email, conn);
				}
			}
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.fatal("Failed rollback, needs to be compensated", e);
				throw new StorageException(e1);
			}
			e.printStackTrace();
			throw new StorageException(e.getMessage());
		}
		long end = System.currentTimeMillis();
		logger.debug("Time to store event: " + (end - start + " ms"));
		return insertedEvent;
	}

	private void storeInvite(int event_id, String email, Connection conn) throws SQLException {
		String insertTableSQL = "insert into event_invites(event,email) values(?,?);";
		PreparedStatement preparedStatement = null;
		preparedStatement = conn.prepareStatement(insertTableSQL);
		preparedStatement.setInt(1, event_id);
		preparedStatement.setString(2, email);
		preparedStatement.executeUpdate();
	}

	private int _putEvent(Event event, Connection conn) throws SQLException {
		verifyEventValid(event);
		String insertTableSQL = "INSERT INTO Event"
				+ "(shortname, description, betDeadline, eventDecidedTime, owner, category,private) VALUES" + "(?,?,?,?,?,?,?)";
		PreparedStatement preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
		preparedStatement.setString(1, event.name);
		preparedStatement.setString(2, event.description);
		preparedStatement.setTimestamp(3, new Timestamp(event.betDeadline.getMillis()));
		preparedStatement.setTimestamp(4, new Timestamp(event.eventDecidedTime.getMillis()));
		preparedStatement.setInt(5, event.owner.id);
		preparedStatement.setInt(6, event.category == null ? Category.NONE.id : event.category.id);
		preparedStatement.setInt(7, event.isPrivate()?1:0);
		preparedStatement.executeUpdate();
		ResultSet rs = preparedStatement.getGeneratedKeys();
		rs.next();
		int auto_id = rs.getInt(1);
		return auto_id;
	}

	private void verifyEventValid(Event event) {
		if (event.description == null || event.description.length() < 1 || event.name == null
				|| event.name.length() < 1)
			throw new IllegalArgumentException("Name or description cannot be null");
		if (event.betDeadline == null || event.eventDecidedTime == null)
			throw new IllegalArgumentException("betDeadline or eventDecidedTime cannot be null");
	}

	private int storeOption(Option option, Connection conn) throws SQLException {
		String insertTableSQL = "INSERT INTO betoption" + "(name, event_id) VALUES" + "(?,?)";
		PreparedStatement preparedStatement = null;
		preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
		preparedStatement.setString(1, option.name);
		preparedStatement.setInt(2, option.event.id);
		preparedStatement.executeUpdate();
		ResultSet rs = preparedStatement.getGeneratedKeys();
		rs.next();
		int auto_id = rs.getInt(1);
		return auto_id;
	}

	@Override
	public void updateEvent(Event event) {
		// TODO Auto-generated method stub

	}

	public List<Event> listEvents(boolean populateWithPlayerLikes) {
		ArrayList<Event> events = new ArrayList<Event>();
		long start = System.currentTimeMillis();
		String selectSQL = "select be.state,p.email,be.private,be.category,be.id,be.shortname,be.description,be.timeofpost,"
				+ "be.betDeadline,be.eventDecidedTime,be.likes,be.pot,im.league,be.invites from bettable_events be left join "
				+ "importedmatch im on im.event=be.id left join player p on be.owner=p.id order by be.betDeadline";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				GenericEvent event = new GenericEvent();
				event.id = rs.getInt("id");
				event.description = rs.getString("description");
				event.name = rs.getString("shortname");
				event.likes = rs.getInt("likes");
				event.eventPosted = new DateTime(rs.getTimestamp("timeofpost").getTime());
				event.betDeadline = new DateTime(rs.getTimestamp("betDeadline").getTime());
				event.eventDecidedTime = new DateTime(rs.getTimestamp("eventDecidedTime").getTime());
				event.state = BetState.valueOf(rs.getString("state"));
				event.category = Category.getCategoryByID(rs.getInt("category"));
				event.pot = rs.getDouble("pot");
				event.league = String.valueOf(rs.getInt("league"));
				boolean privateEvent = rs.getInt("private")==1;
				event.setPrivateEvent(privateEvent);
				if(privateEvent)
					processInvites(event,rs.getString("invites"), rs.getString("email"));
				events.add(event);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		updateWithOptions(events);
		if (populateWithPlayerLikes) {
			for (Iterator<Event> iterator = events.iterator(); iterator.hasNext();) {
				Event event = iterator.next();
				List<Player> ps = fetchPlayerLikes(event);
				event.setLikers(ps);
			}
		}
		long end = System.currentTimeMillis();
		logger.debug("Time to list events: " + (end - start + " ms")+", count is "+ events.size());
		return events;
	}

	private void processInvites(GenericEvent event, String invites, String owner) {
		List<String> invitedList = new ArrayList<String>();
		invitedList.add(owner);
		event.setInvitedList(invitedList );
		if(invites==null)
			return;
		StringTokenizer st = new StringTokenizer(invites,",");
		while(st.hasMoreTokens()){
			String email =st.nextToken();
			logger.debug(String.format("Adding %s to invitedlist for event %s",email,event.name));
			invitedList.add(email);
		}
	}

	private ArrayList<Player> fetchPlayerLikes(Event event) {
		ArrayList<Player> players = new ArrayList<Player>();
		String selectSQL = "select v.player as id,p.firstname,p.email,p.lastname from vote v,player p where v.player=p.id and event=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String firstname = rs.getString("firstname");
				String lastname = rs.getString("lastname");
				String email = rs.getString("email");
				int id = rs.getInt("id");
				Player storedPlayer = new Player(firstname, lastname, email);
				storedPlayer.id = id;
				players.add(storedPlayer);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return players;
	}

	private void updateWithOptions(ArrayList<Event> events) {
		for (Iterator<Event> iterator = events.iterator(); iterator.hasNext();) {
			GenericEvent event = (GenericEvent) iterator.next();
			event.setOptions(getOptionsForEvent(event.id));
		}

	}

	@Override
	public List<Option> getOptionsForEvent(int eventid) {
		ArrayList<Option> options = new ArrayList<Option>();
		String selectSQL = "select name, id from betoption where event_id=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int optionid = rs.getInt("id");
				String name = rs.getString("name");
				Option option = new Option();
				option.id = optionid;
				option.name = name;
				options.add(option);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return options;
	}

	@Override
	public void insertCurrency(Currency currency) {
		String insertTableSQL = "INSERT INTO currency" + "(currency_id,commonName) VALUES" + "(?,?)";
		Connection conn = getDbConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, currency.id);
			preparedStatement.setString(2, currency.name);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public Player storePlayer(Player player) {
		Player storedplayer = player;
		String insertTableSQL = "INSERT INTO player" + "(firstname, lastname,email) VALUES" + "(?,?,?)";
		Connection conn = getDbConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, player.firstname);
			preparedStatement.setString(2, player.lastname);
			preparedStatement.setString(3, player.email);
			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
			storedplayer.id = auto_id;
			return storedplayer;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public boolean playerExists(Player player) {
		String selectSQL = "SELECT id from player WHERE email like ? and activated=1";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		boolean match = true;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, player.email);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (!resultSet.isBeforeFirst()) {
				match = false;
			}
			return match;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public Player getPlayer(Player player) {
		Player storedPlayer = null;
		String selectSQL = "SELECT p.id,p.firstname,p.lastname,p.email,pa.email as ppe,p.p2p from player p "
				+ "left join payment_account pa on pa.player_id=p.id where p.email like ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, player.email);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String firstname = rs.getString("firstname");
				String lastname = rs.getString("lastname");
				String email = rs.getString("email");
				String ppe = rs.getString("ppe");
				int id = rs.getInt("id");
				int p2p = rs.getInt("p2p");
				PaymentAccount pa = new PaymentAccount(ppe,PaymentProvider.PayPal);
				storedPlayer = new Player(firstname, lastname, email);
				storedPlayer.setPaymentAccount(pa);
				storedPlayer.setP2p(p2p==1);
				storedPlayer.id = id;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return storedPlayer;
	}

	public void placeBets(Bet... bets) {
		for (int i = 0; i < bets.length; i++) {
			placeBet(bets[i]);
		}
	}

	@Override
	public boolean currenciesEstablished() {
		String selectSQL = "SELECT count(currency_id) as count from currency";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int count = rs.getInt("count");
				return count > 0;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return false;
	}

	public void setDatasource(DataSource datasource) {
		this.datasource = datasource;
	}

	@Override
	public Event getBettableEvent(int iid) {
		String selectSQL = "SELECT p.email as owneremail, p.firstname as ownerfirstname, p.lastname as ownerlastname, "
				+ "e.owner,e.state,e.id,shortname,description,e.timeofpost, e.betDeadline, e.eventDecidedTime, "
				+ "(select count(id) from vote where event=e.id) as likes FROM event e, player p where e.owner=p.id "
				+ "and state not like 'DELETED' and betDeadline>Current_Timestamp and e.id=?";
		GenericEvent event = getEvent(iid, selectSQL);
		logger.debug("Fetched event of id " + iid + " : " + event);
		if (event == null) {
			throw new NoSuchBettableEventException();
		}
		List<Option> optionsForEvent = getOptionsForEvent(event.id);
		if (optionsForEvent == null || optionsForEvent.isEmpty()) {
			throw new IllegalStateException("Event without options is not a valid event");
		}
		event.setOptions(optionsForEvent);
		return event;
	}

	private GenericEvent getEvent(int iid, String selectSQL) {
		GenericEvent event = null;
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		logger.debug("Exectuting statement in getEvent: " + selectSQL);
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, iid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				event = _processEventRow(rs);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return event;
	}

	private GenericEvent _processEventRow(ResultSet rs) throws SQLException {
		GenericEvent event;
		int eventid = rs.getInt("id");
		String shortname = rs.getString("shortname");
		String description = rs.getString("description");
		int likes = rs.getInt("likes");
		java.sql.Timestamp ts = rs.getTimestamp("timeofpost");
		Player owner = new Player(rs.getString("owneremail"));
		owner.firstname = rs.getString("ownerfirstname");
		owner.lastname = rs.getString("ownerlastname");
		owner.id = rs.getInt("owner");
		event = new GenericEvent();
		event.owner = owner;
		event.id = eventid;
		event.description = description;
		event.name = shortname;
		event.likes = likes;
		event.state = BetState.valueOf(rs.getString("state"));
		event.eventPosted = new DateTime(ts.getTime());
		event.betDeadline = new DateTime(rs.getTimestamp("betDeadline").getTime());
		event.eventDecidedTime = new DateTime(rs.getTimestamp("eventDecidedTime").getTime());
		return event;
	}

	@Override
	public Option getOption(int optionid) {
		Option option = null;
		String selectSQL = "SELECT name,event_id from betoption WHERE id = ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		int event_id = 0;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, optionid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String name = rs.getString("name");
				event_id = rs.getInt("event_id");
				option = new Option();
				option.id = optionid;
				option.name = name;
				option.event_id = event_id;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		option.event = getBettableEvent(event_id);
		return option;
	}

	@Override
	public boolean eventTypesEstablished() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void insertEventType(EventType eventType) {
		String insertTableSQL = "INSERT INTO event_type" + "(event_type_id,name) VALUES" + "(?,?)";
		Connection conn = getDbConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, eventType.getEventType());
			preparedStatement.setString(2, eventType.name());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public FacebookUser getFBUser(String fbUserID, String email) {
		String selectSQL = "select fb_id,facebook_id,email,username,realname,player_id from facebook_account where facebook_id like ? and email like ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		FacebookUser fbuser = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, fbUserID);
			preparedStatement.setString(2, email);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				fbuser = new FacebookUser();
				fbuser.id = rs.getInt("fb_id");
				fbuser.facebook_id = rs.getString("facebook_id");
				fbuser.email = rs.getString("email");
				fbuser.username = rs.getString("username");
				fbuser.realname = rs.getString("realname");
				fbuser.player_id = rs.getInt("player_id");
			}
			return fbuser;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public FacebookUser insertFBUser(FacebookUser fbUser) {
		FacebookUser storedfbuser = fbUser;
		if (fbUser.player_id < 1) {
			throw new IllegalArgumentException("fbUser must have a linked Player, create that first");
		}
		String insertTableSQL = "INSERT INTO facebook_account"
				+ "(username, realname,facebook_id,email,player_id) VALUES" + "(?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, fbUser.username);
			preparedStatement.setString(2, fbUser.realname);
			preparedStatement.setString(3, fbUser.facebook_id);
			preparedStatement.setString(4, fbUser.email);
			preparedStatement.setInt(5, fbUser.player_id);
			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
			storedfbuser.id = auto_id;
			return storedfbuser;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public Player getPlayer(int playerid) {
		Player storedPlayer = null;
		String selectSQL = "SELECT id,firstname,lastname,email,p2p from player WHERE id = ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String firstname = rs.getString("firstname");
				String lastname = rs.getString("lastname");
				String email = rs.getString("email");
				int id = rs.getInt("id");
				int p2p = rs.getInt("p2p");
				storedPlayer = new Player(firstname, lastname, email);
				storedPlayer.setP2p(p2p==1);
				storedPlayer.id = id;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return storedPlayer;

	}

	@Override
	public String getHashedPasswordForUser(String email) {
		String selectSQL = "SELECT password from player WHERE email like ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		String password = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setString(1, email);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				password = rs.getString("password");
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return password;
	}

	@Override
	public Player storePlayerWithPassword(Player player, String hashedpw) {
		Player storedplayer = player;
		String insertTableSQL = "INSERT INTO player" + "(firstname, lastname,email,password) VALUES" + "(?,?,?,?)";
		Connection conn = getDbConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, player.firstname);
			preparedStatement.setString(2, player.lastname);
			preparedStatement.setString(3, player.email);
			preparedStatement.setString(4, hashedpw);
			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
			storedplayer.id = auto_id;
			return storedplayer;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public void deleteTestUsers(String prefix) {

		String deletePlayerSQL = "delete from player where email like ?";
		logger.debug("Executing delete of testusers, email starting with: " + prefix);
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(deletePlayerSQL);
			preparedStatement.setString(1, prefix + "%");
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public void likeEvent(int eventid, Player player) {
		String insertTableSQL = "insert into vote(event,player) values(?,?) " + "ON DUPLICATE KEY UPDATE player=player";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, eventid);
			preparedStatement.setInt(2, player.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public int getLikesForEvent(int eventid) {
		String selectSQL = "select count(id) as count from vote where event=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		int count = 0;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return count;
	}

	@Override
	public boolean hasLiked(int eventid, int playerid) {
		String selectSQL = "select id from vote where event=? and player=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			preparedStatement.setInt(2, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return false;
	}

	@Override
	public void addComment(Player player, int event, String comment) {
		String insertTableSQL = "insert into comment(comment,event,player) values(?,?,?)";
		if (player.id < 1) {
			throw new IllegalArgumentException("player.id<1");
		}
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		logger.info("inside " + getClass().getName() + ", adding comment " + comment);
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, comment);
			preparedStatement.setInt(2, event);
			preparedStatement.setInt(3, player.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public List<Comment> loadCommentsForEvent(int eventid) {
		ArrayList<Comment> comments = new ArrayList<Comment>();
		String selectSQL = "select c.id,c.comment, c.postTime, c.event,p.firstname,p.lastname,p.email from comment c,player p where c.player=p.id and event=? order by c.postTime desc";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String scomment = rs.getString("comment");
				String firstname = rs.getString("firstname");
				String lastname = rs.getString("lastname");
				String email = rs.getString("email");
				java.sql.Timestamp ts = rs.getTimestamp("postTime");
				Player player = new Player(firstname, lastname, email);
				Comment comment = new Comment(id, scomment, player, eventid, new DateTime(ts.getTime()));
				comments.add(comment);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return comments;
	}

	@Override
	public List<Bet> getUserBets(int playerid) {
		ArrayList<Bet> bets = new ArrayList<Bet>();
		String selectSQL = "SELECT betid,option_id,optionname,eventid,amount,currency,"
				+ "shortname,eventdesc,betDeadline,eventDecidedTime,firstname,"
				+ "lastname,email,timeofbet from bets_expanded WHERE player_id = ? "
				+ "and betDeadline>Current_Timestamp";

		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int amount = rs.getInt("amount");
				int currency = rs.getInt("currency");
				int option_id = rs.getInt("option_id");
				int eventid = rs.getInt("eventid");
				int betid = rs.getInt("betid");
				java.sql.Timestamp timeofbet = rs.getTimestamp("timeofbet");
				String firstname = rs.getString("firstname");
				String email = rs.getString("email");
				String lastname = rs.getString("lastname");
				String shortname = rs.getString("shortname");
				String eventdesc = rs.getString("eventdesc");
				String optionname = rs.getString("optionname");
				GenericEvent event = new GenericEvent();
				event.id = eventid;
				event.description = eventdesc;
				event.name = shortname;
				event.betDeadline = new DateTime(rs.getTimestamp("betDeadline"));
				event.eventDecidedTime = new DateTime(rs.getTimestamp("eventDecidedTime"));
				Player player = new Player(firstname, lastname, email);
				player.id = playerid;
				Option option = new Option();
				option.id = option_id;
				option.name = optionname;
				option.event = event;
				Bet bet = new Bet(event, option, amount, currency == 1 ? Currency.NOK : Currency.BETCOIN, player,
						new DateTime(timeofbet.getTime()), betid);
				bets.add(bet);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return bets;
	}

	@Override
	public Wallet getWalletForUser(int uid) {
		String selectSQL = "select id,amount,currency from wallet where wallet.userid=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		double amount = 0;
		Currency currency = null;
		Wallet wallet = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, uid);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				amount = rs.getDouble("amount");
				int curr = rs.getInt("currency");
				int id = rs.getInt("id");
				currency = Currency.getCurrencyByID(curr);
				wallet = new Wallet(amount, uid, currency);
				wallet.id = id;
			} else {
				logger.error("No wallet for user " + uid);
			}

		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return wallet;
	}

	@Override
	public int createEmptyStartupWalletForUser(Player player) {
		String insertTableSQL = "insert into wallet(userid,amount,currency) values " + "(?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL, Statement.RETURN_GENERATED_KEYS);
			logger.debug("creating fresh wallet for player " + player);
			preparedStatement.setInt(1, player.id);
			preparedStatement.setDouble(2, 0);
			preparedStatement.setInt(3, Currency.BETCOIN.id);
			preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			rs.next();
			int auto_id = rs.getInt(1);
			return auto_id;
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public void deletePlayer(int uid) {
		String deletePlayerSQL = "delete from player where id = ?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(deletePlayerSQL);
			preparedStatement.setInt(1, uid);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public void deletePlayerWithEmail(String email) {

		String deletePlayerSQL = "delete from player where email like ?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(deletePlayerSQL);
			preparedStatement.setString(1, email);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public void updateWalletForUser(Wallet w, Player player) {
		String updatesql = "update wallet set amount=? where id=? and userid=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		assert (player.id > 0);
		assert (player.id == w.playerid);
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(updatesql);
			logger.debug("updating wallet for player " + player);
			preparedStatement.setDouble(1, w.getAmount());
			preparedStatement.setInt(2, w.id);
			preparedStatement.setInt(3, player.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public boolean categoriesEstablished() {
		String selectSQL = "SELECT count(categoryid) as count from category";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int count = rs.getInt("count");
				return count > 4;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return false;
	}

	@Override
	public void insertCategory(Category cat) {
		String insertTableSQL = "INSERT INTO category (categoryname, description, categoryid) VALUES (?,?,?)";
		Connection conn = getDbConnection();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, cat.name);
			preparedStatement.setString(2, cat.desc);
			preparedStatement.setInt(3, cat.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public List<String> getTagsForEvent(int eventid) {
		ArrayList<String> taglist = new ArrayList<String>();
		String selectSQL = "SELECT tagname from tag WHERE eventid = ?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, eventid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String tag = rs.getString("tagname");
				taglist.add(tag);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return taglist;
	}

	@Override
	public void addTagsForEvent(int id, List<String> newtags) {
		StringBuffer insertsql = new StringBuffer("insert into tag (tagname,eventid) values ");
		for (Iterator<String> iterator = newtags.iterator(); iterator.hasNext();) {
			String tag = iterator.next();
			insertsql.append("('" + tag + "'," + id + ")");
			if (iterator.hasNext())
				insertsql.append(",");
		}
		String sql = insertsql.toString();
		System.out.println("SQL: " + sql);
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public List<Activity> getActivityLogForPlayer(Player player) {
		ArrayList<Activity> activityList = new ArrayList<Activity>();
		String selectSQL = "select a.activityid,a.activitytype,a.activitytimestamp,"
				+ "a.activityText from activitylog a where user=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, player.id);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int activityid = rs.getInt("activityid");
				int activitytype = rs.getInt("activitytype");
				java.sql.Timestamp timeofActivity = rs.getTimestamp("activitytimestamp");
				DateTime activityDT = new DateTime(timeofActivity.getTime());
				String text = rs.getString("activityText");
				activityList.add(new Activity(text, ActivityType.getById(activitytype), player));
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return activityList;
	}

	@Override
	public void logActivity(ActivityType type, String text, Player player) {
		String insertTableSQL = "insert into activitylog (activitytype,activityText, user) values(?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, type.id);
			preparedStatement.setString(2, text);
			preparedStatement.setInt(3, player.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public void activateEvent(int eventid) {
		_assertActivateOK(getBettableEvent(eventid));
		String updateEventsql = "update event set state='" + BetState.OPEN_FOR_BETTING + "' where id=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(updateEventsql);
			logger.debug("activating event " + eventid);
			preparedStatement.setInt(1, eventid);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	private void _assertActivateOK(Event event) {
		if (event.getState().equals(BetState.INITIAL)) {
			// ok
			logger.debug("Ok to activate, state is " + event.state);
		} else
			throw new IllegalStateException("State needs to be " + BetState.INITIAL);
	}

	@Override
	public List<Event> getEventsInState(BetState state) {
		ArrayList<Event> events = new ArrayList<Event>();
		long start = System.currentTimeMillis();
		String selectSQL = "SELECT e.state, e.id,shortname,description,e.timeofpost,e.betDeadline, e.eventDecidedTime, "
				+ "(select count(id) from vote where event=e.id) as likes, winningoption.option_id as winner FROM event e "
				+ "left join winningoption on e.id=winningoption.event_id "
				+ "where e.state like '"
				+ state.name()
				+ "'";
		logger.info("getEventsInState-sql:  " + selectSQL);
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				GenericEvent event = new GenericEvent();
				event.id = rs.getInt("id");
				event.description = rs.getString("description");
				event.name = rs.getString("shortname");
				event.likes = rs.getInt("likes");
				event.eventPosted = new DateTime(rs.getTimestamp("timeofpost").getTime());
				event.betDeadline = new DateTime(rs.getTimestamp("betDeadline").getTime());
				event.eventDecidedTime = new DateTime(rs.getTimestamp("eventDecidedTime").getTime());
				event.state = BetState.valueOf(rs.getString("state"));
				String winner = rs.getString("winner");
				if (winner != null && winner.length() > 0) {
					event.winningOption = getOptionSkinny(conn,Integer.parseInt(winner));
				}
				events.add(event);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		updateWithOptions(events);
		long end = System.currentTimeMillis();
		logger.debug("Time to list events: " + (end - start + " ms"));
		return events;
	}

	/**
	 * dont populate with eventinfo, and reuse connection
	 * @param conn 
	 * */
	private Option getOptionSkinny(Connection conn, int optionid) {
		Option option = null;
		String selectSQL = "SELECT name,event_id from betoption WHERE id = ?";
		PreparedStatement preparedStatement = null;
		int event_id = 0;
		try {
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, optionid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				String name = rs.getString("name");
				event_id = rs.getInt("event_id");
				option = new Option();
				option.id = optionid;
				option.name = name;
				option.event_id = event_id;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		}
		return option;
	}

	@Override
	public void transitionTo(Event event, BetState state) {
		String updatesql = "update event set state='" + state.name() + "' where id=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(updatesql);
			preparedStatement.setInt(1, event.id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public void betsClosed(List<Event> events) {
		String updatesql = "update event set state='" + BetState.BETTING_CLOSED.name() + "' where id in "
				+ toIdSet(events);
		logger.debug("sql for closing bets: " + updatesql);
		executeUpdateSQL(updatesql);
	}

	private String toIdSet(List<Event> events) {
		StringBuffer set = new StringBuffer("(");
		for (Iterator<Event> iterator = events.iterator(); iterator.hasNext();) {
			Event event = iterator.next();
			set.append(event.id);
			if (iterator.hasNext())
				set.append(",");
		}
		set.append(")");
		return set.toString();
	}

	@Override
	public void eventTimePassed(List<Event> events) {
		String updatesql = "update event set state='" + BetState.EVENT_DECIDED.name() + "' where id in "
				+ toIdSet(events);
		executeUpdateSQL(updatesql);
	}

	private void executeUpdateSQL(String updatesql) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(updatesql);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public Option getWinnerForEvent(Event event) {
		ArrayList<Option> options = new ArrayList<Option>();
		String selectSQL = "select wo.option_id,bo.name from winningoption wo, betoption bo where wo.option_id=bo.id and wo.event_id=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		Option option = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, event.id);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int optionid = rs.getInt("option_id");
				String name = rs.getString("name");
				option = new Option();
				option.id = optionid;
				option.name = name;
				options.add(option);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return option;
	}

	@Override
	public List<Event> getEventsOwnedBy(int playerid) {
		ArrayList<Event> events = new ArrayList<Event>();
		long start = System.currentTimeMillis();
		String selectSQL = "SELECT e.owner,e.state, e.id,shortname,description,e.timeofpost,e.betDeadline, e.eventDecidedTime, "
				+ "(select count(id) from vote where event=e.id) as likes, winningoption.option_id as winner FROM event e "
				+ "left join winningoption on e.id=winningoption.event_id " + "where e.owner=?";
		logger.info("getEventsOwnedBy-sql:  " + selectSQL);
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				GenericEvent event = new GenericEvent();
				event.id = rs.getInt("id");
				event.description = rs.getString("description");
				event.name = rs.getString("shortname");
				event.likes = rs.getInt("likes");
				event.eventPosted = new DateTime(rs.getTimestamp("timeofpost").getTime());
				event.betDeadline = new DateTime(rs.getTimestamp("betDeadline").getTime());
				event.eventDecidedTime = new DateTime(rs.getTimestamp("eventDecidedTime").getTime());
				event.state = BetState.valueOf(rs.getString("state"));
				String winner = rs.getString("winner");
				if (winner != null && winner.length() > 0) {
					event.winningOption = getOptionSkinny(conn,Integer.parseInt(winner));
				}
				events.add(event);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		updateWithOptions(events);
		long end = System.currentTimeMillis();
		logger.debug("Time to getEventsOwnedBy: " + (end - start + " ms"));
		return events;

	}

	@Override
	public Event getEventForDecision(int iid) {
		String selectSQL = "SELECT p.email as owneremail, p.firstname as ownerfirstname, p.lastname as ownerlastname, "
				+ "e.owner,e.state,e.id,shortname,description,e.timeofpost, e.betDeadline, e.eventDecidedTime, "
				+ "(select count(id) from vote where event=e.id) as likes FROM event e, player p where e.owner=p.id "
				+ "and state not like 'DELETED' and state='" + BetState.EVENT_DECIDED.name() + "' and e.id=?";
		GenericEvent event = getEvent(iid, selectSQL);
		logger.debug("Fetched event of id " + iid + " : " + event);
		if (event == null) {
			throw new IllegalArgumentException("Bad input");
		}
		List<Option> optionsForEvent = getOptionsForEvent(event.id);
		if (optionsForEvent == null || optionsForEvent.isEmpty()) {
			throw new IllegalStateException("Event without options is not a valid event");
		}
		event.setOptions(optionsForEvent);
		return event;
	}

	@Override
	public void setWinnerForEvent(int eventid, int winner) {
		String insertTableSQL = "insert into winningoption(event_id,option_id) values(?,?);";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, eventid);
			preparedStatement.setInt(2, winner);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public List<Event> betsToClose() {
		ArrayList<Event> events = new ArrayList<Event>();
		long start = System.currentTimeMillis();
		String selectSQL = "SELECT e.state, e.id,e.shortname,p.id as playerid,p.email FROM event e, player p  where e.owner=p.id and"
				+ " betDeadline < now() and state='" + BetState.OPEN_FOR_BETTING + "'";
		logger.info("betsToClose:  " + selectSQL);
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				events.add(getSkinnyEvent(rs));
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		long end = System.currentTimeMillis();
		logger.debug("Time to list events: " + (end - start + " ms"));
		return events;

	}

	@Override
	public List<Event> betsEventTimePassed() {
		ArrayList<Event> events = new ArrayList<Event>();
		long start = System.currentTimeMillis();
		String selectSQL = "SELECT e.state, e.id,e.shortname,p.id as playerid,p.email FROM event e, player p  where e.owner=p.id and "
				+ "eventDecidedTime < now() and state='" + BetState.BETTING_CLOSED + "'";

		logger.info("betsEventTimePassed:  " + selectSQL);
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				events.add(getSkinnyEvent(rs));
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		long end = System.currentTimeMillis();
		logger.debug("Time to list events: " + (end - start + " ms"));
		return events;
	}

	private GenericEvent getSkinnyEvent(ResultSet rs) throws SQLException {
		GenericEvent event = new GenericEvent();
		event.id = rs.getInt("id");
		event.name = rs.getString("shortname");
		event.state = BetState.valueOf(rs.getString("state"));
		Player owner = new Player(rs.getString("email"));
		owner.id = rs.getInt("playerid");
		event.owner = owner;
		return event;
	}

	@Override
	public double settleBet(Bet bet, EventOdds eodds) {
		assertEventInCorrectState(BetState.EVENT_DECIDED, bet);
		Option winningOption = bet.getEvent().getWinningOption();
		double odds = eodds.get(winningOption);
		boolean winner = winningOption.getName().equals(bet.getOption().getName());
		double amount_won = odds * bet.amount;

		if (winner) {// only payout if winner...
			String updatewalletsql = "update wallet set amount = amount + ? where id=? and userid=?";
			int playerid = bet.player.id;
			Wallet wallet = getWalletForUser(playerid);
			if (wallet == null) {
				throw new IllegalStateException("No wallet for player " + playerid);
			}
			Connection conn = null;
			PreparedStatement preparedStatementUpdateWallet = null;
			try {
				conn = getDbConnection();
				preparedStatementUpdateWallet = conn.prepareStatement(updatewalletsql);
				preparedStatementUpdateWallet.setDouble(1, amount_won);
				preparedStatementUpdateWallet.setInt(2, wallet.id);
				preparedStatementUpdateWallet.setInt(3, playerid);
				preparedStatementUpdateWallet.executeUpdate();
			} catch (SQLException e) {
				logger.error(e);
				try {
					conn.rollback();
				} catch (SQLException e1) {
					logger.fatal("Failed rollback, needs to be compensated", e);
					throw new StorageException(e);
				}
				throw new StorageException(e);
			} finally {
				closeGracefully(conn, preparedStatementUpdateWallet);
			}

		}
		return amount_won;
	}

	private void assertEventInCorrectState(BetState state, Bet bet) {
		if (bet == null || bet.event == null)
			throw new IllegalStateException("Bet needs to be connected to an event in correct state");
		if (!state.equals(bet.event.state))
			throw new IllegalStateException("Event needs to be in state " + state + ", but is in state "
					+ bet.event.state);

	}

	@Override
	public void storeImportedMatchEvent(MatchEvent matchEvent, RemoteMatch rmatch) {
		assertOkToStore(matchEvent, rmatch);
		Event stored = storeEvent(matchEvent);
		registerImport(stored.id, rmatch.match_static_id,Integer.parseInt(rmatch.match_comp_id));
	}

	private void registerImport(int evid, long match_static_id, int match_comp_id) {
		String insertTableSQL = "insert into importedmatch (event,matchid, league) values (?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, evid);
			preparedStatement.setInt(2, (int) match_static_id);
			preparedStatement.setInt(3, match_comp_id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	private void assertOkToStore(MatchEvent matchEvent, RemoteMatch rmatch) {
		if (matchEvent == null || rmatch == null || rmatch.match_static_id == 0) {
			throw new IllegalArgumentException("Not ok");
		}
		String me = getRemoteMatch(rmatch.match_static_id);
		if (me != null)
			throw new MatchAlreadyImportedException("Already imported");
	}

	private String getRemoteMatch(long mid) {
		String selectSQL = "select event,importtime from importedmatch where matchid=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		String match_event = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, (int) mid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				int event = rs.getInt("event");
				match_event = mid + "_" + event;
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return match_event;
	}

	@Override
	public void storeTransfer(Transfer transfer) {

		String insertTableSQL = "insert into transfer(player_id_from,player_id_to,bet,amount) " + "values(?,?,?,?)";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, transfer.getFrom().id);
			preparedStatement.setInt(2, transfer.getTo().id);
			preparedStatement.setInt(3, transfer.getBetid());
			preparedStatement.setDouble(4, transfer.getAmount());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}

	}

	@Override
	public List<Transfer> getUnsettledTransfersForUser(int player_id) {
		String selectSQL = "select t.id,t.amount,t.player_id_from as pid_from,t.player_id_to as player_id_to,pa_from.email as email_from, pa_to.email "
				+ "as email_to,t.creation from transfer t, payment_account pa_from, payment_account as pa_to where"
				+ " t.player_id_from=pa_from.player_id and t.player_id_to=pa_to.player_id and "
				+ "t.completed=0 and t.player_id_from=?";
		logger.info("Query is " + selectSQL);
		PreparedStatement preparedStatement = null;
		List<Transfer> ts = new ArrayList<Transfer>();
		Connection conn = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, player_id);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				Player from = new Player(rs.getString("email_from"),rs.getInt("pid_from"));
				Player to = new Player(rs.getString("email_to"),rs.getInt("player_id_to"));
				Transfer transfer = new Transfer(rs.getInt("id"),from,to, rs.getDouble("amount"));
				ts.add(transfer);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return ts;

	}

	@Override
	public PaymentAccount getPaymentAccountForUser(int player_id) {
		String selectSQL = "select a.email,p.name from payment_account a, payment_provider p where a.provider_id=p.id and player_id=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		PaymentAccount pa = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, player_id);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				pa = new PaymentAccount(rs.getString("email"), PaymentProvider.valueOf(rs.getString("name")));
			} else {
				logger.error("No PaymentAccount For User " + player_id);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return pa;
	}

	@Override
	public void setPaymentAccountForUser(int player_id, String email) {
		String insertsql = "insert into payment_account(email,provider_id,player_id) values(?,1,?) on duplicate key update email=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertsql);
			preparedStatement.setString(1, email);
			preparedStatement.setInt(2, player_id);
			preparedStatement.setString(3, email);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public void setPaidTransfer(int transferid) {
		String updatesql = "update transfer set completedTime=now(), completed=1 where id=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(updatesql);
			preparedStatement.setInt(1, transferid);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public Event setMatchResult(int importedmatchid, String result) {
		Event event = assertOKToSetResult(importedmatchid);
		String insertsql = "insert into matchresult (impmatchid,result) values (?,?);";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertsql);
			preparedStatement.setInt(1, importedmatchid);
			preparedStatement.setString(2, result);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return event;
	}
	
	private Event assertOKToSetResult(int importedmatchid) {	
		String me = getRemoteMatch(importedmatchid);
		if (me == null){
			throw new NoSuchImportedMatchException("No such imported match : "+importedmatchid);
		}
		String res = getMatchResult(importedmatchid);
		if(res!=null){
			throw new IllegalStateException("Matchresult already set for match : "+importedmatchid);
		}
		logger.debug("Fetching event for setting result");
		Event event;
		try {
			StringTokenizer st = new StringTokenizer(me,"_");
			st.nextToken();
			String eventid=st.nextToken();
			logger.debug("Fetching event for setting result, eventid is "+eventid);
			event = getEventForDecision(Integer.parseInt(eventid));
		} catch (NumberFormatException e) {
			logger.error(e);
			throw new IllegalStateException("Failed loading event for match : "+importedmatchid+", identifier is " + me);
		}
		logger.debug("Fetching event for setting result, done, event is " + event);
		return event;
	}

	@Override
	public String getMatchResult(int matchid) {
		String selectSQL = "select result from matchresult where impmatchid=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		String res = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, matchid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				res = rs.getString("result");
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return res;
	}

	@Override
	public void setPaymode(int playerid, boolean p2p) {
		String insertTableSQL = "update player set p2p=? where id=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, p2p ? 1 : 0);
			preparedStatement.setInt(2, playerid);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}		
	}

	@Override
	public void activatePlayer(int playerid) {
		String insertTableSQL = "update player set activated=1 where id=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(insertTableSQL);
			preparedStatement.setInt(1, playerid);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
	}

	@Override
	public boolean isActivated(int playerid) {
		String selectSQL = "select activated from player where id=?";
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		int res = 0;
		try {
			conn = getDbConnection();
			preparedStatement = conn.prepareStatement(selectSQL);
			preparedStatement.setInt(1, playerid);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				res = rs.getInt("activated");
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new StorageException(e);
		} finally {
			closeGracefully(conn, preparedStatement);
		}
		return res==1;
		
	}

}
